const express = require("express");
const router = express.Router();
const dayjs = require("dayjs");
const utc = require("dayjs/plugin/utc");
const timezone = require("dayjs/plugin/timezone");

dayjs.extend(utc);
dayjs.extend(timezone);

let connectDAO = require("./connect.js");

// 获取预约记录
router.get("/reserve_seat", function (req, res) {
  const {
    page = 1,
    pageSize = 10,
    ReserveSeatID,
    UserID,
    RoomID,
    Date,
  } = req.query;

  let pageNum = parseInt(page);
  let pageSizeNum = parseInt(pageSize);
  if (isNaN(pageNum)) pageNum = 1;
  if (isNaN(pageSizeNum)) pageSizeNum = 10;
  const offset = (pageNum - 1) * pageSizeNum;
  let baseSql = `SELECT * FROM ReserveSeat`;
  let countSql = `SELECT COUNT(*) AS total FROM ReserveSeat`;
  const conditions = [];

  // 筛选条件（保持您原有的拼接方式）
  if (ReserveSeatID) {
    conditions.push(`ReserveSeatID = '${ReserveSeatID}'`);
  }
  if (UserID) {
    conditions.push(`UserID = '${UserID}'`);
  }
  if (RoomID) {
    conditions.push(`RoomID = '${RoomID}'`);
  }
  if (Date) {
    conditions.push(`Date = '${Date}'`);
  }

  let whereClause = "";
  if (conditions.length > 0) {
    whereClause = " WHERE " + conditions.join(" AND ");
    baseSql += whereClause;
    countSql += whereClause;
  }

  // 分页查询
  const dataSql = `${baseSql} LIMIT ${pageSizeNum} OFFSET ${offset}`;

  // 先查询总数
  connectDAO.queryDataBySQL(countSql, (countResult) => {
    const total = countResult[0].total;

    // 再查询分页数据
    connectDAO.queryDataBySQL(dataSql, (dataResult) => {
      res.send({
        code: 200,
        msg: "Success",
        data: {
          records: dataResult,
          pagination: {
            current: pageNum,
            pageSize: pageSizeNum,
            total: total,
          },
        },
      });
    });
  });
});

// 预约座位
router.post("/reserve_seat", function (req, res) {
  const { UserID, UserName, RoomID, RoomName, Time, Date } = req.body;
  if (!UserID || !RoomID || !Time || !Date || !UserName || !RoomName) {
    res.send({
      code: 400,
      msg: "Missing required fields",
    });
    return;
  }
  const sql = `INSERT INTO ReserveSeat (UserID,UserName, RoomID, RoomName,Time,Date) VALUES ('${UserID}','${UserName}', '${RoomID}', '${RoomName}','${Time}','${Date}')`;
  connectDAO.queryDataBySQL(sql, () => {
    res.send({
      code: 200,
      msg: "Success",
    });
  });
});

// 更新预约
router.put("/update_reserve_seats", function (req, res) {
  const { ReserveSeatID, RoomID, RoomName, Time, Date } = req.body;
  const sql = `UPDATE ReserveSeat SET RoomID = ${RoomID}, RoomName = '${RoomName}', Time = '${Time}', Date = '${Date}' WHERE ReserveSeatID = ${ReserveSeatID}`;
  connectDAO.queryDataBySQL(sql, () => {
    res.send({
      code: 200,
      msg: "Success",
    });
  });
});

// 取消预约
router.put("/cancel_reserve_seats", function (req, res) {
  const { ReserveSeatID } = req.body;
  if (!ReserveSeatID) {
    res.send({
      code: 400,
      msg: "Missing required fields",
    });
    return;
  }
  const sql = `UPDATE ReserveSeat SET Cancel = '1' WHERE ReserveSeatID = '${ReserveSeatID}'`;
  connectDAO.queryDataBySQL(sql, () => {
    res.send({
      code: 200,
      msg: "Success",
    });
  });
});

// 入座
router.put("/be_seat", function (req, res) {
  const { ReserveSeatID } = req.body;
  const now = dayjs().format("YYYY-MM-DD HH:mm:ss");
  const sql = `UPDATE ReserveSeat SET BeSeatedTime = '${now}' WHERE ReserveSeatID = '${ReserveSeatID}'`;
  connectDAO.queryDataBySQL(sql, () => {
    res.send({
      code: 200,
      msg: "Success",
    });
  });
});

// 离座
router.put("/leave_seat", function (req, res) {
  const { ReserveSeatID } = req.body;
  const now = dayjs().format("YYYY-MM-DD HH:mm:ss");
  const sql = `UPDATE ReserveSeat SET LeaveSeatTime = '${now}' WHERE ReserveSeatID = '${ReserveSeatID}'`;
  connectDAO.queryDataBySQL(sql, () => {
    res.send({
      code: 200,
      msg: "Success",
    });
  });
});

// 获取近7天预约统计
router.get(
  "/appointment_statistics_in_the_past_seven_days",
  function (req, res) {
    // 计算日期范围（过去7天，包含今天）
    const today = dayjs().format("YYYY-MM-DD");
    const sevenDaysAgo = dayjs().subtract(6, "day").format("YYYY-MM-DD"); // 6天前+今天=7天

    // 查询SQL（使用Date字段）
    const sql = `
    SELECT 
      Date, 
      COUNT(*) as count
    FROM ReserveSeat
    WHERE Date BETWEEN '${sevenDaysAgo}' AND '${today}'
      AND Cancel = '0'  -- 只统计未取消的预约
    GROUP BY Date
    ORDER BY Date ASC
  `;

    // 执行查询
    connectDAO.queryDataBySQL(sql, (result) => {
      // 生成完整的7天日期数组（过去7天）
      const dateMap = {};
      for (let i = 0; i < 7; i++) {
        const date = dayjs()
          .subtract(6 - i, "day")
          .format("YYYY-MM-DD"); // 从6天前到今天的日期
        dateMap[date] = 0;
      }

      // 填充查询结果
      result.forEach((row) => {
        dateMap[row.Date] = row.count;
      });

      // 转换为数组格式
      const stats = Object.keys(dateMap).map((date) => ({
        date,
        count: dateMap[date],
      }));

      res.send({
        code: 200,
        msg: "Success",
        data: stats,
      });
    });
  }
);

module.exports = router;
